package sgc.beans;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.faces.context.FacesContext;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.primefaces.event.RowEditEvent;

import sgc.struc.Pord_Header;
import sgc.struc.Pord_Position;
import sgc.struc.Req_Position;
import sgc.util.*;

@ManagedBean(name = "purchaseOrderBean")
@SessionScoped
public class PurchaseOrderBean implements Serializable {

	private static final long serialVersionUID = 7243677153195728454L;
	private List<String> requisition;
	private Pord_Header header;
	private Pord_Position position;
	private List<Pord_Position> positions;
	private int conta;
	private String user_name;
	private String user_id;

	private DataSource ds;

	public PurchaseOrderBean() {
		try {
			Context ctx = new InitialContext();
			ds = (DataSource) ctx.lookup("java:comp/env/jdbc/sgcweb");
		} catch (NamingException e) {
			e.printStackTrace();
		}
		header = new Pord_Header();
		position = new Pord_Position();
		positions = new ArrayList<Pord_Position>();
		positions.clear();

		FacesContext context = FacesContext.getCurrentInstance();
		CheckUserBean cu = (CheckUserBean) context.getApplication()
				.evaluateExpressionGet(context, "#{checkUserBean}",
						CheckUserBean.class);

		user_name = cu.getUsuario().getDescrip();
		user_id = cu.getUsuario().getName();

	}

	public List<String> getRequisition() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_getAllReqHeader_2(?, ?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, Util.act_company());
		cs.setString(2, "Aprobada");
		cs.execute();
		ResultSet result = cs.getResultSet();

		List<String> list = new ArrayList<String>();

		while (result.next()) {
			list.add(Integer.toString(result.getInt(2)));
		}
		con.close();

		return list;

	}

	public void setRequisition(List<String> requisition) {
		this.requisition = requisition;
	}

	public Pord_Header getHeader() {
		return header;
	}

	public void setHeader(Pord_Header header) {
		this.header = header;
	}

	public Pord_Position getPosition() {
		return position;
	}

	public void setPosition(Pord_Position position) {
		this.position = position;
	}

	public List<Pord_Position> getPositions() {
		return positions;
	}

	public void setPositions(List<Pord_Position> positions) {
		this.positions = positions;
	}

	public void Order_Fill() throws SQLException {

		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_getReqHeader(?, ?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, Util.act_company());
		cs.setInt(2, header.getPreq_no());
		cs.execute();
		ResultSet result = cs.getResultSet();

		while (result.next()) {
			header.setPor_no(0);
			header.setCompany_code(Util.act_company());
			header.setFec_por(result.getDate(3));
			header.setFec_req(result.getDate(4));
			header.setSolicitante_name(result.getString(5));
			header.setSuplier_name(result.getInt("sup_code") + " - "
					+ result.getString("sup_name3") + " "
					+ result.getString("sup_name4") + ", "
					+ result.getString("sup_name1") + " "
					+ result.getString("sup_name2"));
			header.setCecos_name(result.getString("cecos_cod") + " - "
					+ result.getString("cecos_descrip"));
			header.setPor_deta(result.getString(13));
		}
		simpleProc = "{ CALL sp_getReqPosition(?, ?) }";
		CallableStatement cs2 = con.prepareCall(simpleProc);
		cs2.setInt(1, Util.act_company());
		cs2.setInt(2, header.getPreq_no());
		cs2.execute();
		ResultSet result2 = cs2.getResultSet();
		positions.clear();
		while (result2.next()) {
			Pord_Position aux_pos = new Pord_Position();

			aux_pos.setPor_pos(result2.getInt(2));
			aux_pos.setMat_name(result2.getInt(3) + " - "
					+ result2.getString(4));
			aux_pos.setMat_cant((int) result2.getDouble(6));
			aux_pos.setMat_unit(result2.getString(8));
			positions.add(aux_pos);
		}

		con.close();

	}

	public void onEdit(RowEditEvent event) throws SQLException {
		// CallableStatement cs;
		// String simpleProc;
		// ResultSet result;
		//
		// Pord_Position aux_pos;
		// int aux_index;
		// int aux_mat;
		// String aux_umed;
		//
		//
		// if (ds == null)
		// throw new SQLException("Can't get data source");
		// Connection con = ds.getConnection();
		// if (con == null)
		// throw new SQLException("Can't get database connection");
		//
		// aux_pos = (Pord_Position) event.getObject();
		// aux_index = aux_pos.getPor_pos() - 1;
		// aux_mat = Util.parseInt(aux_pos.getMat_name());
		//
		// simpleProc = "{ CALL sp_getMaterial(?,?) }";
		// cs = con.prepareCall(simpleProc);
		//
		// cs.setInt(1, aux_mat);
		// cs.setInt(2, Util.act_company());
		// cs.execute();
		// result = cs.getResultSet();
		// result.next();
		// aux_umed = result.getString("unit");
		// aux_pos.setMat_unit(aux_umed);
		// positions.set(aux_index, position);

	}

	public void onCancel(RowEditEvent event) {
	}

	public void Save() throws SQLException {

		String aux_message;
		String aux_message_p;
		ResultSet result;
		CallableStatement cs;
		String simpleProc;
		Iterator<Pord_Position> it;
		Pord_Position posicion;
		int por_no;
		int req_no;
		int por_pos_p;
		int por_pos;

		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		// Cabecera de registro

		simpleProc = "{ CALL sp_addPorHeader(?,?,?,?,?,?,?,?) }";
		cs = con.prepareCall(simpleProc);

		cs.setInt(1, Util.act_company());
		cs.setDate(2, Util.convertDate(header.getFec_por()));
		cs.setDate(3, Util.convertDate(header.getFec_req()));
		cs.setString(4, user_id);
		cs.setInt(5, Util.parseInt(header.getSuplier_name()));
		cs.setString(6, Util.parseStr(header.getCecos_name()));
		cs.setString(7, header.getPor_deta());
		cs.setInt(8, header.getPreq_no());

		cs.execute();
		result = cs.getResultSet();
		result.next();
		aux_message = result.getString(3);
		por_no = result.getInt(2);
		if (por_no == 0) {
			FacesContext.getCurrentInstance().addMessage(
					null,
					new FacesMessage(FacesMessage.SEVERITY_ERROR, aux_message,
							null));
		} else {
			// Posiciones
			it = positions.iterator();
			por_pos = 0;
			while (it.hasNext()) {
				por_pos++;
				posicion = it.next();
				simpleProc = "{ CALL sp_addPorPosition(?,?,?,?,?) }";
				cs = con.prepareCall(simpleProc);

				cs.setInt(1, por_no);
				cs.setInt(2, por_pos);
				cs.setInt(3, Util.parseInt(posicion.getMat_name()));
				cs.setDouble(4, posicion.getMat_cant());
				cs.setInt(5, Util.act_company());
				cs.execute();
				result = cs.getResultSet();
				result.next();
				por_pos_p = result.getInt(3);
				aux_message_p = result.getString(4);
				if (por_pos_p == 0) {
					FacesContext.getCurrentInstance().addMessage(
							null,
							new FacesMessage(FacesMessage.SEVERITY_ERROR,
									aux_message_p, null));
				}
			}

			simpleProc = "{ CALL sp_updReqHeader(?,?,?,?) }";
			cs = con.prepareCall(simpleProc);

			cs.setInt(1, Util.act_company());
			cs.setInt(2, header.getPreq_no());
			cs.setString(3, user_id);
			cs.setString(4, "Completa");
			cs.execute();
			result = cs.getResultSet();
			result.next();
			aux_message = result.getString(2);
			req_no = result.getInt(1);
			if (req_no == 0) {
				FacesContext.getCurrentInstance().addMessage(
						null,
						new FacesMessage(FacesMessage.SEVERITY_ERROR,
								aux_message, null));
			} else {
				FacesContext.getCurrentInstance().addMessage(
						null,
						new FacesMessage(FacesMessage.SEVERITY_INFO,
								aux_message, null));

				header = null;
				positions = null;
				position = null;
			}

		}
	}
}
